﻿using JsonConversion;
using Newtonsoft.Json;
using RTracker.Models.Common;
using RTracker.Models.ErrorLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;

namespace RTracker.Areas.Recruitment.Models.VacancyExpirence
{
    public class MVacancyExpirence
    {
        /// static variable RowSize from config settings to get list of this row size.
        public static int nRowSize = Convert.ToInt32(ConfigurationManager.AppSettings["TotalRecordsInListUser"]);

        /// <summary>
        /// This method is used to create a Vacancy Expirence in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about authentication context.</param>
        /// <param name="objCVacancy">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details</returns>
        public CStatus CreateVacancyExpirence(CAuthenticationContext objCAuthenticationContext, ref CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();

            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_create_vacancy_expirence_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = objCVacancyExpirence.OrganizationId;
                    cmd.Parameters.Add("@client_id", SqlDbType.Int).Value = objCVacancyExpirence.ClientId;
                    cmd.Parameters.Add("@vacancy_expirence_name", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceName;
                    cmd.Parameters.Add("@vacancy_expirence_desc", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceDesc;
                    cmd.Parameters.Add("@min_expirence_years", SqlDbType.Int).Value = objCVacancyExpirence.MinExpirenceYears;
                    cmd.Parameters.Add("@min_expirence_months", SqlDbType.Int).Value = objCVacancyExpirence.MinExpirenceMonths;
                    cmd.Parameters.Add("@max_expirence_years", SqlDbType.Int).Value = objCVacancyExpirence.MaxExpirenceYears;
                    cmd.Parameters.Add("@max_expirence_months", SqlDbType.Int).Value = objCVacancyExpirence.MaxExpirenceMonths;
                    cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }


            return oCStatus;
        }

        /// <summary>
        /// This method is used to delete a VacancyExpirence in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about  authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus DeleteVacancyExpirence(CAuthenticationContext objCAuthenticationContext, ref CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();

            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_delete_vacancy_expirence_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;
                    cmd.Parameters.Add("@VacancyExpirence_ids", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceIds;

                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }
            return oCStatus;
        }

        /// <summary>
        /// This method is used to update the VacancyExpirence's detail in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus UpdateVacancyExpirence(CAuthenticationContext objCAuthenticationContext, ref CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();

            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_update_vacancy_expirence_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@vacancy_expirence_id", SqlDbType.Int).Value = objCVacancyExpirence.VacancyExpirenceId; ;
                    cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = objCVacancyExpirence.OrganizationId;
                    cmd.Parameters.Add("@client_id", SqlDbType.Int).Value = objCVacancyExpirence.ClientId;
                    cmd.Parameters.Add("@vacancy_expirence_name", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceName;
                    cmd.Parameters.Add("@vacancy_expirence_desc", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceDesc;
                    cmd.Parameters.Add("@min_expirence_years", SqlDbType.Int).Value = objCVacancyExpirence.MinExpirenceYears;
                    cmd.Parameters.Add("@min_expirence_months", SqlDbType.Int).Value = objCVacancyExpirence.MinExpirenceMonths;
                    cmd.Parameters.Add("@max_expirence_years", SqlDbType.Int).Value = objCVacancyExpirence.MaxExpirenceYears;
                    cmd.Parameters.Add("@max_expirence_months", SqlDbType.Int).Value = objCVacancyExpirence.MaxExpirenceMonths;
                    cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;


                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }

            return oCStatus;
        }

        /// <summary>
        /// This method is used to get the VacancyExpirence's detail in the database.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about  authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus GetVacancyExpirenceDetail(CAuthenticationContext objCAuthenticationContext, ref CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();
            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("r_get_vacancy_expirence_detail_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@vacancy_expirence_id", SqlDbType.Int).Value = objCVacancyExpirence.VacancyExpirenceId;
                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    DataTable dt = new DataTable();

                    int rowsAffected = adp.Fill(dt);

                    if (rowsAffected > 0)
                    {
                        objCVacancyExpirence = dt.ConvertToListFromDataTableVacancyExpirence()[0];
                    }

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.DetailSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }
            return oCStatus;
        }

        /// <summary>
        /// Sets the softlock on the record whose Id is passed.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about VacancyExpirence's authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus SetVacancyExpirenceSoftLock(CAuthenticationContext objCAuthenticationContext, CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();

            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_set_vacancy_expirence_soft_lock_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@vacancy_expirence_id", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceId;
                    cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }


            return oCStatus;
        }

        /// <summary>
        /// Sets the hardlock on the record whose Id is passed.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about VacancyExpirence's authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus SetVacancyExpirenceHardLock(CAuthenticationContext objCAuthenticationContext, CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();

            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_set_vacancy_expirence_hard_lock_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@vacancy_expirence_id", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceId;
                    cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }


            return oCStatus;
        }

        /// <summary>
        /// Releases the softlock on the record whose Id is passed.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about VacancyExpirence's authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus ReleaseVacancyExpirenceSoftLock(CAuthenticationContext objCAuthenticationContext, CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();

            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_release_vacancy_expirence_soft_lock_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@vacancy_expirence_id", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceId;
                    cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }


            return oCStatus;
        }

        /// <summary>
        /// Releases the hardlock on the record whose Id is passed.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about VacancyExpirence's authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus ReleaseVacancyExpirenceHardLock(CAuthenticationContext objCAuthenticationContext, CVacancyExpirence objCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();

            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_release_vacancy_expirence_hard_lock_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@vacancy_expirence_id", SqlDbType.NVarChar).Value = objCVacancyExpirence.VacancyExpirenceId;
                    cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = objCAuthenticationContext.UserId;

                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }


            return oCStatus;
        }

        /// <summary>
        ///  This function is used to get complete VacancyExpirence list
        /// </summary>
        /// <returns>List of type CVacancyExpirence</returns>
        public GVacancyExpirence GetVacancyExpirenceGridList(CVacancyExpirence objCVacancyExpirence, CAuthenticationContext objCAuthenticationContext)
        {
            GVacancyExpirence objGVacancyExpirence = new GVacancyExpirence();
            int nTotalRecords = 0;
            int nCurrentRowSize = nRowSize;

            if (objCVacancyExpirence.RowSize != 0)
            {
                nCurrentRowSize = objCVacancyExpirence.RowSize;
            }
            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("r_get_vacancy_expirence_paging_list_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@vacancy_expirence_id", SqlDbType.Int).Value = objCVacancyExpirence.VacancyExpirenceId;
                    cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = objCVacancyExpirence.OrganizationId;
                    cmd.Parameters.Add("@page_number", SqlDbType.Int).Value = objCVacancyExpirence.PageNumber;
                    cmd.Parameters.Add("@row_size", SqlDbType.Int).Value = nCurrentRowSize;
                    cmd.Parameters.Add("@sort_on", SqlDbType.VarChar).Value = objCVacancyExpirence.SortOn;
                    cmd.Parameters.Add("@sort_type", SqlDbType.VarChar).Value = objCVacancyExpirence.SortType;
                    cmd.Parameters.Add("@search", SqlDbType.VarChar).Value = objCVacancyExpirence.Search;
                    cmd.Parameters.Add("@searchon", SqlDbType.VarChar).Value = objCVacancyExpirence.SearchOn;
                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    cmd.Parameters["@error"].Direction = ParameterDirection.Output;
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    DataTable dt = new DataTable();

                    int rowsAffected = adp.Fill(dt);

                    objGVacancyExpirence.Paging = new CPaging();
                    if (rowsAffected > 0)
                    {
                        objGVacancyExpirence.LstVacancyExpirence = dt.ConvertToListFromDataTableVacancyExpirence();
                        objGVacancyExpirence.LstVacancyExpirence[0].PageNumber = int.Parse(cmd.Parameters["@idOut"].Value.ToString());
                        objGVacancyExpirence.Paging.PageNumber = objGVacancyExpirence.LstVacancyExpirence[0].PageNumber;
                    }
                    else
                    {
                        objGVacancyExpirence.LstVacancyExpirence = null;
                    }


                    nTotalRecords = int.Parse(cmd.Parameters["@error"].Value.ToString());

                    objGVacancyExpirence.Status = new CStatus();

                    if (nTotalRecords < 0)
                    {
                        objGVacancyExpirence.Status.Success = false;
                        objGVacancyExpirence.Status.StatusDetails = nTotalRecords.DbError();
                    }
                    else
                    {
                        objGVacancyExpirence.Paging.TotalRecordsCount = nTotalRecords;
                        objGVacancyExpirence.Paging.PageSize = nCurrentRowSize;

                        objGVacancyExpirence.Status.Success = true;
                        objGVacancyExpirence.Status.StatusDetails = "success";
                    }



                }
                catch (Exception ex)
                {
                    objGVacancyExpirence.Status = new CStatus();
                    objGVacancyExpirence.Status.Success = false;
                    objGVacancyExpirence.Status.StatusDetails = ex.Message;
                }
            }
            return objGVacancyExpirence;
        }
        
        /// /// <summary>
        ///  This function is used to get complete VacancyExpirence list working under a same organization.
        /// </summary>
        /// <param name="objCAuthenticationContext">This object contains information about VacancyExpirence's authentication context.</param>
        /// <param name="objCVacancyExpirence">This object contains details of the VacancyExpirence.</param>
        /// <param name="lstCVacancyExpirence">"out" the list of VacancyExpirences</param>
        /// <returns>CStatus which is an object that contain transaction details.</returns>
        public CStatus GetVacancyExpirenceList(CAuthenticationContext objCAuthenticationContext, int organizationId, out List<CVacancyExpirence> lstCVacancyExpirence)
        {
            CStatus oCStatus = new CStatus();
            lstCVacancyExpirence = new List<CVacancyExpirence>();
            using (SqlConnection con = new SqlConnection(CCommonCollection.ConnectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("r_get_vacancy_expirence_list_procedure", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@organization_id", SqlDbType.Int).Value = organizationId;

                    cmd.Parameters.Add("@idOut", SqlDbType.Int).Value = null;
                    cmd.Parameters["@idOut"].Direction = ParameterDirection.Output;
                    //cmd.Parameters.Add("@error", SqlDbType.Int).Value = null;
                    //cmd.Parameters["@error"].Direction = ParameterDirection.Output;
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    DataTable dt = new DataTable();

                    int rowsAffected = adp.Fill(dt);

                    if (rowsAffected > 0)
                    {
                        lstCVacancyExpirence.AddRange(dt.ConvertToListFromDataTableVacancyExpirence());
                    }

                    if (Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()) > 0)
                    {
                        oCStatus.Success = true;
                        oCStatus.StatusDetails = CAppConstants.SessionSuccess;
                    }
                    else
                    {
                        oCStatus.Success = false;
                        oCStatus.StatusDetails = Convert.ToInt32(cmd.Parameters["@idOut"].Value.ToString()).DbError();
                        //oCStatus.StatusDetails = CAppErrorsConstants.SomethingWentWrong;
                    }
                }
                catch (Exception ex)
                {
                    oCStatus.Success = false;
                    oCStatus.StatusDetails = ex.Message;
                }
            }


            return oCStatus;
        }


    }
}